package com.sky.db.meta;

import com.sky.db.meta.entity.ColumnInfo;
import com.sky.db.meta.entity.TableInfo;

import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class DbHelper {

    public JdbcHelper jdbcHelper = null;


    public DbHelper() {
        try {
            jdbcHelper = new JdbcHelper();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public DbHelper(String jdbcUrl, String user, String password) {
        try {
            jdbcHelper = new JdbcHelper(jdbcUrl, user, password);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    public static void main(String args[]) {
        try {

            List<String> list = new DbHelper().getUserDbList();

            List<TableInfo> tableInfoList = new DbHelper().getTableListByDbName("sb_demo");

            List<ColumnInfo> columnInfoInfoList = new DbHelper().getColumnListByTableName("sb_demo", "User");

            TableInfo tableInfo = new DbHelper().getTableInfo("sb_demo", "User");

            Integer a = 1;

        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    // region  获取所有用户数据库,即不含默认库
    public List<String> getUserDbList() throws Exception {
        List<String> list = new ArrayList<String>();
        String sql = "SELECT\n" +
                "\tlower( schema_name ) schemaName \n" +
                "FROM\n" +
                "\tinformation_schema.schemata \n" +
                "WHERE\n" +
                "\tschema_name NOT IN ( 'mysql', 'information_schema', 'test', 'search', 'tbsearch', 'sbtest', 'dev_ddl', 'performance_schema' )";

        ResultSet resultSet = jdbcHelper.connection.createStatement().executeQuery(sql);
        while (resultSet.next()) {
            String columnValue = resultSet.getString("schemaName");
            list.add(columnValue);
        }
        return list;
    }
    //endregion

    //region 根据数据库名称找表信息
    public List<TableInfo> getTableListByDbName(String dbName) {
        String sql = "SELECT\n" +
                "\ttable_name tableName,\n" +
                "\ttable_rows tableRows,\n" +
                "\ttable_comment tableComment\n" +
                "FROM\n" +
                "\tinformation_schema.TABLES\n" +
                "WHERE\n" +
                "\ttable_schema = ?";

        List<TableInfo> tableInfoList = jdbcHelper.getForList(TableInfo.class, sql, dbName);

        return tableInfoList;
    }
    //endregion

    //region 根据数据库名称找表信息
    public TableInfo getTableInfoByDbNameAndTbName(String dbName, String tbName) {
        String sql = "SELECT\n" +
                "\ttable_name tableName,\n" +
                "\ttable_rows tableRows,\n" +
                "\ttable_comment tableComment\n" +
                "FROM\n" +
                "\tinformation_schema.TABLES\n" +
                "WHERE\n" +
                "\ttable_schema = ?" +
                "\tAND table_name = ?";

        TableInfo tableInfo = jdbcHelper.getEntity(TableInfo.class, sql, dbName, tbName);

        return tableInfo;
    }
    //endregion

    //region 根据库名和表名找所有字段
    public List<ColumnInfo> getColumnListByTableName(String dbName, String tableName) {
        String sql = "SELECT\n" +
                "\tlower(column_name) columnName,\n" +
                "\tlower(TABLE_SCHEMA) schemeName,\n" +
                "\tlower(TABLE_NAME) tableName,\n" +
                "\tIFNULL('a',column_default) defaultValue,\n" +
                "\tIF(is_nullable='YES',1,0)  canNull,\n" +
                "\tcolumn_type originType,\n" +
                "\tcolumn_comment comment,\n" +
                "\tIFNULL(character_maximum_length,0)  maxLength,\n" +
                "\tIF(COLUMN_KEY='PRI',1,0) isPrimaryKey\n" +
                "\n" +
                "FROM\n" +
                "\tinformation_schema. COLUMNS\n" +
                "WHERE\n" +
                "\ttable_schema = ? \n" +
                "AND table_name = ? \n" +
                "ORDER BY ordinal_position ASC;";

        List<ColumnInfo> columnInfoList = jdbcHelper.getForList(ColumnInfo.class, sql, dbName, tableName);

        for (ColumnInfo info :
                columnInfoList)
            info.setOriginTypeWithoutLen(info.getOriginType().substring(0, info.getOriginType().indexOf("(", 0)));

        return columnInfoList;
    }
    //endregion

    //region 根据库名和表名找表信息含所有字段
    //region 根据库名和表名找所有字段
    public TableInfo getTableInfo(String dbName, String tableName) {

        TableInfo tableInfo = getTableInfoByDbNameAndTbName(dbName, tableName);

        List<ColumnInfo> columnInfoList = getColumnListByTableName(dbName, tableName);

        tableInfo.setColumnInfoList(columnInfoList);

        return tableInfo;
    }
    //endregion
    //endregion
}
